﻿create procedure dbo.FindTextNeighbors 
(@FtTableName nvarchar(100),
@KeyName nvarchar(100),
@TxtFields nvarchar(100),
@Key int) AS
begin 
	declare @TxtContent nvarchar(4000);
	declare @Sql nvarchar(4000);
	set @Sql=N'select @TxtContent='+@TxtFields+' from '+@FtTableName+' where '+@KeyName+'='+cast(@Key as nvarchar);
	declare @ParamDef nvarchar(1000);
	set @ParamDef='@TxtContent nvarchar(4000) out';
	exec sp_executesql @Sql, @ParamDef, @TxtContent out;

	set @Sql=N'
	SELECT
		KeyTbl.[Key],
		KeyTbl.Rank
	FROM
		FreeTextTable ('+@FtTableName+', '+@TxtFields+','+dbo.QuoteString(@TxtContent)+') KeyTbl
		INNER JOIN '+@FtTableName+' FtTbl ON
		FtTbl.'+@KeyName+' = KeyTbl.[Key] 
	WHERE KeyTbl.[Key]<>'+cast(@Key as nvarchar)+
	' order by KeyTbl.Rank desc';

	exec sp_executesql @Sql;
end 